CREATE OR REPLACE PACKAGE BODY DISCOUNT_USER.insertcouponfeed_pkg AS


 PROCEDURE InsertUpdateDISCOUNTS_LNKSHR(
 
    
    p_advertisername IN LINKSHARE_DISCOUNTS.ADVERTISER_NAME%TYPE,
    p_advertiserid IN LINKSHARE_DISCOUNTS.ADVERTISER_ID%TYPE,
    p_offerdescription IN LINKSHARE_DISCOUNTS.OFFER_DESCRIPTION%TYPE,
    p_offerstartdate IN LINKSHARE_DISCOUNTS.OFFER_START_DATE%TYPE,
    p_offerenddate IN LINKSHARE_DISCOUNTS.OFFER_END_DATE%TYPE,
    p_impressionpixel IN LINKSHARE_DISCOUNTS.IMPRESSION_PIXEL%TYPE,
    p_networkid IN LINKSHARE_DISCOUNTS.NETWORK_ID%TYPE,
    p_network IN LINKSHARE_DISCOUNTS.NETWORK%TYPE,
    p_couponcode IN LINKSHARE_DISCOUNTS.COUPON_CODE%TYPE,
    p_offerid IN LINKSHARE_DISCOUNTS.OFFER_ID%TYPE,
    p_clickurl IN LINKSHARE_DISCOUNTS.CLICK_URL%TYPE)
   
    IS
   
 l_exists INTEGER;

BEGIN
   
      SELECT COUNT(*) INTO l_exists  FROM LINKSHARE_DISCOUNTS  WHERE OFFER_ID = p_offerid  AND ROWNUM = 1;
   IF l_exists = 1 THEN
       
        UPDATE LINKSHARE_DISCOUNTS
        SET ADVERTISER_NAME =  p_advertisername,
            ADVERTISER_ID =  p_advertiserid
           
            WHERE OFFER_ID = p_offerid;
 
  ELSE
  
    INSERT INTO LINKSHARE_DISCOUNTS(ADVERTISER_NAME,ADVERTISER_ID,OFFER_DESCRIPTION,OFFER_START_DATE,OFFER_END_DATE,IMPRESSION_PIXEL,NETWORK_ID,NETWORK,COUPON_CODE,OFFER_ID,CLICK_URL)
    VALUES (
    
    p_advertisername,
    p_advertiserid,
    p_offerdescription,
    p_offerstartdate,
    p_offerenddate,
    p_impressionpixel,
    p_networkid,
    p_network,
    p_couponcode,
    p_offerid,
    p_clickurl);
  END IF;
  
   
END InsertUpdateDISCOUNTS_LNKSHR;


   

 PROCEDURE InsertUpdateLNKSHR_SUBCAT(
   
    p_catid IN LINKSHARE_CATEGORY.CATEGORY_ID%TYPE,
    p_catname IN LINKSHARE_CATEGORY.CATEGORY_NAME%TYPE,
    p_offerid IN LINKSHARE_DISCOUNTS.OFFER_ID%TYPE)
   
    IS
   
  l_exists  INTEGER ;
BEGIN
   
      SELECT COUNT(*) INTO l_exists  FROM LINKSHARE_CATEGORY  WHERE CATEGORY_ID = p_catid  AND ROWNUM = 1;

   IF l_exists = 1 THEN
        UPDATE LINKSHARE_CATEGORY
            SET CATEGORY_NAME =   p_catname
        WHERE CATEGORY_ID = p_catid;
 
  ELSE
    INSERT INTO LINKSHARE_CATEGORY(CATEGORY_ID,CATEGORY_NAME)
    VALUES (p_catid,p_catname);
  END IF;
  
     l_exists :=0;
     SELECT COUNT(*) INTO l_exists  FROM LINKSHARE_CATEGORY_XREF  WHERE CATEGORY_ID = p_catid  AND OFFER_ID=p_offerid AND ROWNUM = 1;

IF l_exists = 0 THEN     
    INSERT INTO LINKSHARE_CATEGORY_XREF (OFFER_ID,CATEGORY_ID)
    VALUES (p_offerid,p_catid);
  END IF;
   
END InsertUpdateLNKSHR_SUBCAT;




PROCEDURE InsertUpdateLNKSHR_PROMO(
   
    p_promoid IN LINKSHARE_PROMOTYPE.PROMOTION_TYPE_ID%TYPE,
    p_promoname IN LINKSHARE_PROMOTYPE.PROMOTION_NAME%TYPE,
    p_offerid IN LINKSHARE_DISCOUNTS.OFFER_ID%TYPE)
   
    IS
   
  l_exists INTEGER;
BEGIN
   
      SELECT COUNT(*) INTO l_exists  FROM LINKSHARE_PROMOTYPE  WHERE PROMOTION_TYPE_ID = p_promoid  AND ROWNUM = 1;

   IF l_exists = 1 THEN
        UPDATE LINKSHARE_PROMOTYPE
            SET PROMOTION_NAME =   p_promoname
        WHERE PROMOTION_TYPE_ID = p_promoid;
 
  ELSE
    INSERT INTO LINKSHARE_PROMOTYPE(PROMOTION_TYPE_ID,PROMOTION_NAME)
    VALUES (p_promoid,p_promoname);
  END IF;
  
     l_exists :=0;
     SELECT COUNT(*) INTO l_exists  FROM LINKSHARE_PROMOTYPE_XREF  WHERE PROMOTION_TYPE_ID = p_promoid  AND OFFER_ID=p_offerid AND ROWNUM = 1;

IF l_exists = 0 THEN     
    INSERT INTO LINKSHARE_PROMOTYPE_XREF(OFFER_ID,PROMOTION_TYPE_ID)
    VALUES (p_offerid,p_promoid);
  END IF;
   
END InsertUpdateLNKSHR_PROMO;



END insertcouponfeed_pkg;
/